import pymysql
import numpy as np
import sys
sys.path.append('D:\Users\dale\anaconda3\Lib\site-packages/pymysql')

# 连接 MySQL 数据库
conn = pymysql.connect(host='localhost', user='root', password='root', database='StudentInfo')

# 执行 SQL 查询语句，获取数据
cursor = conn.cursor()
cursor.execute('SELECT StudentNo, name, jwid,JW,jzid,JZ,OSid,OS,DSid,DS FROM student_info')
data = cursor.fetchall()

# 关闭连接
cursor.close()
conn.close()


# 将数据转换为关系矩阵
student_ids = list(set([d[0] for d in data]))
# print(student_ids)
student_names = list(set([d[1] for d in data]))
# print(student_names)

# course_id = list(set([d[] for d in data]))

jwid = list(set([d[2] for d in data]))
print(jwid)
JW = list(set([d[3] for d in data]))

# print(JW)
jzid = list(set([d[4] for d in data]))
JZ = list(set([d[5] for d in data]))
# print(JZ)
OSid = list(set([d[6] for d in data]))
OS = list(set([d[7] for d in data]))
# print(OS)
DSid = list(set([d[8] for d in data]))
DS = list(set([d[9] for d in data]))
# print(DS)
user_index = {student_ids[i]: i for i in range(len(student_ids))}
# print(user_index)
# item_index = {student_names[i]: i for i in range(len(student_names))}
item_index = {jwid[i]: i for i in range(len(jwid))}
print(item_index)
# R1 = np.zeros((len(student_ids), len(student_names)))
R1 = np.zeros((len(student_ids), len(jwid)))
R2= np.zeros((len(student_ids), len(student_names)))
# print(R)
for d in data:
    # user_id = d[0]
    # item_id = d[1]
    # score = d[2]
    # R[user_index[user_id], item_index[item_id]] = score
    # student_names = d[0]
    student_ids = d[0]
    print(student_ids)
    jw_id = d[2]
    print(jw_id)
    jwscore = d[3]
    print(jwscore)
    jz_id = d[4]
    print(jz_id)
    jzscore = d[5]
    print(jzscore)
    R1[user_index[student_ids], item_index[student_ids]] = jwscore
    # R2[user_index[student_names], item_index[jz_id]] = jzscore

print(R1)
# print(R2)

#
# C = np.corrcoef(R.T)
#
# print(C)